Databases and DBUPGRADE

A deployment of Planning Space uses two types of database to store all of the system, application and user data:

• a single 'common database' (also known as the 'common data source') is used to store all of the system data for IPS Server and the common elements of the tenants
• each tenant has its own 'tenant database' which stores tenant-specific system data, and users' data.

Database Recovery Model

The choice for Recovery Model will depend on the overall policy for backup and disaster recovery (DR) of the Planning Space deployment. The 'Simple' recovery model keeps the transaction log growth to a minimum and provides for best database performance. However this will entail a DR approach that is based on full and differential backups, in line with the required recovery point objective (RPO). If High Availability (always on, or mirroring) is a requirement, and/or point-in-time data restoration, then the 'Full' recovery model will be needed.

Database security

Important: There are two security levels of SQL Server account required for IPS/Planning Space deployment. For runtime access to the Common and Tenant databases, a restricted account should always be used (with server role 'bulkadmin' and the roles 'db_datareader', 'db_datawriter', and 'pes_datawriter' for the database). In a production environment a separate SQL Server account should be used for each database. For running DBUPGRADE programs during installation and upgrading, a more privileged account is required, with the role 'db_owner' for the database which is being upgraded.

DBUPGRADE programs

Two programs, IPS DBUPGRADE and DBUPGRADE, are provided to prepare a database for use as the common database or as a tenant database. Each program can be used in two ways:

• to start from a new, empty database and create the necessary table structures and configuration which are required for the installed version of IPS Server or Planning Space, or
• to start from an existing database that has been used with an earlier version of IPS Server or Planning Space, and apply an upgrade process to transform the database tables to the required structure for the current version of IPS/Planning Space.

Detailed instructions for using IPS DBUPGRADE and DBUPGRADE are provided in IPS common database creation and IPS DBUPGRADE, and Create the initial tenant and database (DBUPGRADE).

The DBUPGRADE programs automatically check the state of a given database and will determine the creation or transformation steps required. If a database is already in a ready-to-use state then DBUPGRADE will detect this and will not make any changes to it.

Important: The DBUPGRADE programs have a 'minimum versioning' functionality (introduced since version 16.3 SP1). This means that upgrades only work on databases that are configured for the 'last' version, that is, a version 16.5 DBUPGRADE only works on existing databases that are configured for version 16.4 (or an earlier release of 16.5). Databases configured for earlier versions must be upgraded by amulti-step process.

Important for Planning Space deployments upgrading from versions 16.3 or 16.4: For optimal performance it is recommended for every database to set the option 'Legacy Cardinality Estimation' to 'ON'.

Command line programs

The IPS DBUPGRADE and DBUPGRADE folders each contain a command line program 'Palantir.DBUpgrade.Command.exe'.

Run the program in PowerShell to see all of its input parameters: .\Palantir.DBUpgrade.Command.exe /?

Note that the program will run an upgrade immediately given valid inputs, therefore make sure that you have made full backups of databases before attempting to use this program.